﻿using System;
using System.Configuration;
using System.Data;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Dapper;
using System.Linq;

namespace OAuthServerApi
{
    public class DbOAuthHelper : IDisposable
    {
        #region 数据库连接
        private IDbConnection _connection;

        private void Open(string connection)
        {
            if (_connection == null)
            {
                _connection = new MySqlConnection(connection);
            }
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
        }

        public DbOAuthHelper(string connectionString)
        {
            Open(connectionString);
        }

        public DbOAuthHelper()
        {
            Open(ConfigurationManager.AppSettings["UsedConnectionString"]);
        }
        #endregion

        #region Token
        /// <summary>
        /// 添加token
        /// </summary>
        /// <param name="token"></param>
        /// <returns></returns>
        public async Task<int> AddToken(OAuthToken token,int responseType = 0)
        {
            var sql = string.Empty;
            if (token.GrantType == (int)RequestGrantType.password)
            {
                sql += "DELETE FROM oauth_access_token WHERE userHash = @userHash and clientHash = @clientHash and grantType = @grantType;";
            }
            else if (token.GrantType == (int)RequestGrantType.client_credentials)
            {
                sql += "DELETE FROM oauth_access_token WHERE clientHash = @clientHash and grantType = @grantType;";
            }
            else if (token.GrantType == (int)RequestGrantType.authorization_code)
            {
                sql += "DELETE FROM oauth_access_token WHERE userHash = @userHash and clientHash = @clientHash and grantType = @grantType;";
                if (responseType == (int)ResponseType.code)
                {
                    sql += "DELETE FROM oauth_grant_code WHERE userHash = @userHash and clientHash = @clientHash and responseType="+responseType+";";
                }
            }
            sql += @"INSERT oauth_access_token(accessToken,refreshToken,ticket,clientHash,userHash,experiesDate,refreshExpDate,grantType,createDate) 
                        VALUES(@accessToken,@refreshToken,@ticket,@clientHash,@userHash,@experiesDate,@refreshExpDate,@grantType,@createDate)";
            var param = new
            {
                accessToken = new DbString { Value = token.AccessToken, IsFixedLength = true, Length = 100, IsAnsi = true },
                refreshToken = new DbString { Value = token.RefreshToken, IsFixedLength = true, Length = 100, IsAnsi = true },
                ticket = new DbString { Value = token.Ticket, IsFixedLength = true, Length = 1000, IsAnsi = true },
                clientHash = token.ClientHash,
                userHash = token.UserHash,
                experiesDate = token.ExperiesDate,
                refreshExpDate = token.RefreshExpDate,
                grantType = token.GrantType,
                createDate = DateTime.Now
            };
            IDbTransaction tran = _connection.BeginTransaction();
            try
            {
                var result = await _connection.ExecuteAsync(sql, param, tran);
                tran.Commit();
                return result;
            }
            catch
            {
                tran.Rollback();
                throw new AggregateException("执行错误");
            }
        }
        /// <summary>
        /// 删除原有token生成新token
        /// </summary>
        /// <param name="orignalId"></param>
        /// <param name="token"></param>
        /// <returns></returns>
        public async Task<int> DeleteWithAddToken(int orignalId, OAuthToken token)
        {
            var sql = @"DELETE FROM oauth_access_token WHERE accessTokenId = @accessTokenId;INSERT oauth_access_token(accessToken,refreshToken,ticket,clientHash,userHash,experiesDate,refreshExpDate,grantType,createDate) 
                        VALUES(@accessToken,@refreshToken,@ticket,@clientHash,@userHash,@experiesDate,@refreshExpDate,@grantType,@createDate)";
            var param = new
            {
                accessTokenId = orignalId,
                accessToken = new DbString { Value = token.AccessToken, IsFixedLength = true, Length = 120, IsAnsi = true },
                refreshToken = new DbString { Value = token.RefreshToken, IsFixedLength = true, Length = 80, IsAnsi = true },
                ticket = new DbString { Value = token.Ticket, IsFixedLength = true, Length = 500, IsAnsi = true },
                clientHash = token.ClientHash,
                userHash = token.UserHash,
                experiesDate = token.ExperiesDate,
                refreshExpDate = token.RefreshExpDate,
                grantType = token.GrantType,
                createDate = DateTime.Now
            };
            IDbTransaction tran = _connection.BeginTransaction();
            try
            {
                var result = await _connection.ExecuteAsync(sql, param, tran);
                tran.Commit();
                return result;
            }
            catch
            {
                tran.Rollback();
                throw new AggregateException("执行错误");
            }
        }
        /// <summary>
        /// 根据refreshToken更新token
        /// </summary>
        /// <param name="orignalId"></param>
        /// <param name="token"></param>
        /// <returns></returns>
        public async Task<int> UpdateToken(int orignalId, OAuthToken token)
        {
            var sql = @"Update oauth_access_token set accessToken=@accessToken,refreshToken=@refreshToken,experiesDate=@experiesDate,refreshExpDate=@refreshExpDate  WHERE accessTokenId = @accessTokenId;";
            var param = new
            {
                accessTokenId = orignalId,
                accessToken = new DbString { Value = token.AccessToken, IsFixedLength = true, Length = 100, IsAnsi = true },
                refreshToken = new DbString { Value = token.RefreshToken, IsFixedLength = true, Length = 100, IsAnsi = true },
                experiesDate = token.ExperiesDate,
                refreshExpDate = token.RefreshExpDate,
            };
            return await _connection.ExecuteAsync(sql, param);
        }

        /// <summary>
        /// 根据刷新令牌获取token
        /// </summary>
        /// <param name="refreshToken"></param>
        /// <returns></returns>
        public async Task<OAuthToken> GetOAuthToken(string refreshToken)
        {
            var param = new
            {
                refreshToken = new DbString { Value = refreshToken, IsFixedLength = true, Length = 100, IsAnsi = true }
            };
            var list = await _connection.QueryAsync<OAuthToken>("SELECT * from oauth_access_token WHERE refreshToken =@refreshToken  Limit 1", param);
            return list.SingleOrDefault();
        }
        #endregion

        #region Client
        /// <summary>
        /// 根据clientHash获取单个client
        /// </summary>
        /// <param name="clientHash"></param>
        /// <returns></returns>
        public async Task<OAuthClient> GetOAuthClient(string clientHash)
        {
            var param = new
            {
                clientHash = new DbString { Value = clientHash, IsFixedLength = true, Length = 16, IsAnsi = true },
            };
            var list = await _connection.QueryAsync<OAuthClient>("SELECT * from oauth_client WHERE clientHash =@clientHash  Limit 1", param);
            return list.SingleOrDefault();
        }
        /// <summary>
        /// 添加应用client
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public async Task<int> AddOAuthClient(OAuthClient client)
        {
            var sql = @"INSERT oauth_client(clientHash,clientName,clientSecret,clientRedirectUri,createDate) 
                        VALUES(@clientHash,@clientName,@clientSecret,@clientRedirectUri,@createDate)";
            var param = new
            {
                clientHash = new DbString { Value = client.ClientHash, IsFixedLength = true, Length = 16, IsAnsi = true },
                clientName = new DbString { Value = client.ClientName, IsFixedLength = true, Length = 20, IsAnsi = true },
                clientSecret = new DbString { Value = client.ClientSecret, IsFixedLength = true, Length = 16, IsAnsi = true },
                clientRedirectUri = new DbString { Value = client.ClientRedirectUri, IsFixedLength = true, Length = 200, IsAnsi = true },
                createDate = DateTime.Now
            };
            return await _connection.ExecuteAsync(sql, param);
        }
        #endregion

        #region Code
        /// <summary>
        /// 添加一个授权码
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public async Task<int> AddCode(OAuthCode code)
        {
            var sql = "DELETE FROM oauth_grant_code WHERE responseType=@responseType and userHash = @userHash and clientHash = @clientHash;";
            sql += @"INSERT oauth_grant_code(grantCode,ticket,clientHash,userHash,experiesDate,responseType,createDate) 
                        VALUES(@grantCode,@ticket,@clientHash,@userHash,@experiesDate,@responseType,@createDate)";
            var param = new
            {
                grantCode = new DbString { Value = code.GrantCode, IsFixedLength = true, Length = 100, IsAnsi = true },
                ticket = new DbString { Value = code.Ticket, IsFixedLength = true, Length = 400, IsAnsi = true },
                clientHash = code.ClientHash,
                userHash = code.UserHash,
                experiesDate = code.ExperiesDate,
                responseType = code.ResponseType,
                createDate = DateTime.Now
            };
            IDbTransaction tran = _connection.BeginTransaction();
            try
            {
                var result = await _connection.ExecuteAsync(sql, param, tran);
                tran.Commit();
                return result;
            }
            catch
            {
                tran.Rollback();
                throw new AggregateException("执行错误");
            }
        }
        /// <summary>
        /// 删除授权码
        /// </summary>
        /// <param name="codeId"></param>
        /// <returns></returns>
        public async Task<int> DelCode(int codeId)
        {
            var sql = "DELETE FROM oauth_grant_code WHERE grantCodeId = @grantCodeId;";
            return await _connection.ExecuteAsync(sql, new{ grantCodeId = codeId });
        }
        /// <summary>
        /// 获取授权码信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public async Task<OAuthCode> GetOAuthCode(string code)
        {
            var param = new
            {
                grantCode = new DbString { Value = code, IsFixedLength = true, Length = 100, IsAnsi = true }
            };
            var list = await _connection.QueryAsync<OAuthCode>("SELECT * from oauth_grant_code WHERE grantCode =@grantCode  Limit 1", param);
            return list.SingleOrDefault();
        }
        #endregion

        #region User
        /// <summary>
        /// 获取登录用户
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public async Task<OAuthUser> GetUser(string name, string pwd)
        {
            var param = new
            {
                UserName = new DbString { Value = name, IsFixedLength = true, Length = 50, IsAnsi = true },
                UserPassword = new DbString { Value = pwd, IsFixedLength = true, Length = 50, IsAnsi = true }
            };
            var list = await _connection.QueryAsync<OAuthUser>("SELECT userHash,userName from oauth_user WHERE userName =@UserName  and userPassword=@userPassword Limit 1", param);
            return list.SingleOrDefault();
        }

        #endregion

        #region 数据库关闭
        public void Dispose()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
        }
        #endregion

    }
}